package edu.xmu.software.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import edu.xmu.software.pojo.Item;

public class ItemDAO {
	private JdbcTemplate jdbcTemplate;
	
	public void setDataSource(DataSource dataSource){
		jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	public Item getLatestItemById(int id){
		final String QUERY_BY_ID = "SELECT * from items, revisions where items.item_latest = revisions.rev_id and items.item_id = ?";
		Connection conn = null;
		try{
			conn = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = conn.prepareStatement(QUERY_BY_ID);
			queryPs.setInt(1, id);
			ResultSet rs = queryPs.executeQuery();
			if (rs.next()){
				Item it = new Item();
				it.setTitle(rs.getString("item_title"));
				it.setType(rs.getInt("item_type"));
				it.setEditor(rs.getString("editor"));
				it.setEditTime(rs.getString("time"));
				it.setContent(rs.getString("content"));
				it.setItemId(rs.getInt("item_id"));
				it.setRevId(rs.getInt("rev_id"));
				return it;
			}
		}catch (SQLException exc){
			exc.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public List<Item> getLatestItems(int pageNum){ 
		List<Item> retValue = new ArrayList<Item>();
		final String QUERY_ALL = "SELECT * from items, revisions where items.item_latest = revisions.rev_id order by items.item_latest DESC LIMIT 0, ?";
		Connection conn = null;
		try{
			conn = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = conn.prepareStatement(QUERY_ALL);
			queryPs.setInt(1, pageNum);
			ResultSet rs = queryPs.executeQuery();
			while (rs.next()){
				Item it = new Item();
				it.setTitle(rs.getString("item_title"));
				it.setType(rs.getInt("item_type"));
				it.setEditor(rs.getString("editor"));
				it.setEditTime(rs.getString("time"));
				it.setContent(rs.getString("content"));
				it.setItemId(rs.getInt("item_id"));
				it.setRevId(rs.getInt("rev_id"));
				retValue.add(it);
			}
			return retValue;
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public List<Item> getAllItems(){ 
		List<Item> retValue = new ArrayList<Item>();
		final String QUERY_ALL = "SELECT * from items, revisions where items.item_latest = revisions.rev_id";
		Connection conn = null;
		try{
			conn = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = conn.prepareStatement(QUERY_ALL);
			ResultSet rs = queryPs.executeQuery();
			while (rs.next()){
				Item it = new Item();
				it.setTitle(rs.getString("item_title"));
				it.setType(rs.getInt("item_type"));
				it.setEditor(rs.getString("editor"));
				it.setEditTime(rs.getString("time"));
				it.setContent(rs.getString("content"));
				it.setItemId(rs.getInt("item_id"));
				it.setRevId(rs.getInt("rev_id"));
				retValue.add(it);
			}
			return retValue;
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public List<Item> getWordItems(int offset, int num){ 
		List<Item> retValue = new ArrayList<Item>();
		final String QUERY_ALL = "SELECT * from items, revisions where items.item_latest = revisions.rev_id and items.item_type=2 LIMIT ?, ?";
		Connection conn = null;
		try{
			conn = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = conn.prepareStatement(QUERY_ALL);
			queryPs.setInt(1, offset);
			queryPs.setInt(2, num);
			ResultSet rs = queryPs.executeQuery();
			while (rs.next()){
				Item it = new Item();
				it.setTitle(rs.getString("item_title"));
				it.setType(rs.getInt("item_type"));
				it.setEditor(rs.getString("editor"));
				it.setEditTime(rs.getString("time"));
				it.setContent(rs.getString("content"));
				it.setItemId(rs.getInt("item_id"));
				it.setRevId(rs.getInt("rev_id"));
				retValue.add(it);
			}
			return retValue;
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public List<Item> getFileItems(int offset, int num){ 
		List<Item> retValue = new ArrayList<Item>();
		final String QUERY_ALL = "SELECT * from items, revisions where items.item_latest = revisions.rev_id and items.item_type=3 LIMIT ?, ?";
		Connection conn = null;
		try{
			conn = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = conn.prepareStatement(QUERY_ALL);
			queryPs.setInt(1, offset);
			queryPs.setInt(2, num);
			ResultSet rs = queryPs.executeQuery();
			while (rs.next()){
				Item it = new Item();
				it.setTitle(rs.getString("item_title"));
				it.setType(rs.getInt("item_type"));
				it.setEditor(rs.getString("editor"));
				it.setEditTime(rs.getString("time"));
				it.setContent(rs.getString("content"));
				it.setItemId(rs.getInt("item_id"));
				it.setRevId(rs.getInt("rev_id"));
				retValue.add(it);
			}
			return retValue;
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public List<Item> getCatelogItems(int offset, int num){ 
		List<Item> retValue = new ArrayList<Item>();
		final String QUERY_ALL = "SELECT * from items, revisions where items.item_latest = revisions.rev_id and items.item_type=1 LIMIT ?, ?";
		Connection conn = null;
		try{
			conn = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = conn.prepareStatement(QUERY_ALL);
			queryPs.setInt(1, offset);
			queryPs.setInt(2, num);
			ResultSet rs = queryPs.executeQuery();
			while (rs.next()){
				Item it = new Item();
				it.setTitle(rs.getString("item_title"));
				it.setType(rs.getInt("item_type"));
				it.setEditor(rs.getString("editor"));
				it.setEditTime(rs.getString("time"));
				it.setContent(rs.getString("content"));
				it.setItemId(rs.getInt("item_id"));
				it.setRevId(rs.getInt("rev_id"));
				retValue.add(it);
			}
			return retValue;
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public boolean addNewItem(Item it) {
		final String INSERT_SQL1 = "INSERT INTO items (item_title, item_type) value (?, ?)";
		final String INSERT_SQL2 = "INSERT INTO revisions (item_id, content, editor, time) value (?, ?, ?, ?)";
		final String UPDATE_SQL = "UPDATE items set item_latest = ? where item_id = ?";
		Connection connection = null;
		try{
			System.out.println("ddiie");
			connection = jdbcTemplate.getDataSource().getConnection();
			String[] columnNames1= {"item_id"} ;
			PreparedStatement insertPs = connection.prepareStatement(INSERT_SQL1, columnNames1);
			insertPs.setString(1, it.getTitle());
			insertPs.setInt(2, it.getType());
			insertPs.addBatch();
			insertPs.executeBatch();
			ResultSet rs = insertPs.getGeneratedKeys();
			System.out.println("124");
			rs.next();
			int item_id = rs.getInt(1);
			System.out.println("itid: " + item_id);
			String[] columnNames2= {"rev_id"} ;
			insertPs = connection.prepareStatement(INSERT_SQL2, columnNames2);
			insertPs.setInt(1, item_id);
			insertPs.setString(2, it.getContent());
			insertPs.setString(3, it.getEditor());
			insertPs.setString(4, it.getEditTime());
			insertPs.addBatch();
			insertPs.executeBatch();
			System.out.println("125");
			rs = insertPs.getGeneratedKeys();
			rs.next();
			int rev_id = rs.getInt(1);
			PreparedStatement updatePs = connection.prepareStatement(UPDATE_SQL);
			updatePs.setInt(1, rev_id);
			updatePs.setInt(2, item_id);
			updatePs.executeUpdate();
			System.out.println("eeee" + rev_id +"ee" + item_id);
			return true;
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;	
	}

	public List<Item> getAllRevsById(int itemId) {
		final String QUERY_BY_ID = "SELECT * from items, revisions where items.item_id = ? and revisions.item_id = ?";
		Connection conn = null;
		List<Item> retValue = new ArrayList<Item>();
		try{
			conn = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = conn.prepareStatement(QUERY_BY_ID);
			queryPs.setInt(1, itemId);
			queryPs.setInt(2, itemId);
			ResultSet rs = queryPs.executeQuery();
			while (rs.next()){
				Item it = new Item();
				it.setTitle(rs.getString("item_title"));
				it.setType(rs.getInt("item_type"));
				it.setEditor(rs.getString("editor"));
				it.setEditTime(rs.getString("time"));
				it.setContent(rs.getString("content"));
				it.setItemId(rs.getInt("item_id"));
				it.setRevId(rs.getInt("rev_id"));
				retValue.add(it);
			}
			return retValue;
		}catch (SQLException exc){
			exc.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public Item getItemByIdAndRev(int itemId, int revId) {
		final String QUERY_BY_ID = "SELECT * from items, revisions where items.item_id = ? and revisions.rev_id = ?";
		Connection conn = null;
		try{
			conn = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = conn.prepareStatement(QUERY_BY_ID);
			queryPs.setInt(1, itemId);
			queryPs.setInt(2, revId);
			ResultSet rs = queryPs.executeQuery();
			if (rs.next()){
				Item it = new Item();
				it.setTitle(rs.getString("item_title"));
				it.setType(rs.getInt("item_type"));
				it.setEditor(rs.getString("editor"));
				it.setEditTime(rs.getString("time"));
				it.setContent(rs.getString("content"));
				it.setItemId(rs.getInt("item_id"));
				it.setRevId(rs.getInt("rev_id"));
				return it;
			}
		}catch (SQLException exc){
			exc.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public boolean updateItem(Item it) {		
		final String INSERT_SQL = "INSERT INTO revisions (item_id, content, editor, time) value (?, ?, ?, ?)";
		final String UPDATE_SQL = "UPDATE items set item_latest = ? where item_id = ?";
		Connection connection = null;
		try{
			//if (getItemByIdAndRev(it.getItemId(), it.getItemId()) == null)
			//	return false;
			connection = jdbcTemplate.getDataSource().getConnection();
			String[] columnNames2= {"rev_id"} ;
			PreparedStatement insertPs = connection.prepareStatement(INSERT_SQL, columnNames2);
			insertPs.setInt(1, it.getItemId());
			insertPs.setString(2, it.getContent());
			insertPs.setString(3, it.getEditor());
			insertPs.setString(4, it.getEditTime());
			insertPs.addBatch();
			insertPs.executeBatch();
			ResultSet rs = insertPs.getGeneratedKeys();
			rs.next();
			int rev_id = rs.getInt(1);
			PreparedStatement updatePs = connection.prepareStatement(UPDATE_SQL);
			updatePs.setInt(1, rev_id);
			updatePs.setInt(2, it.getItemId());
			updatePs.executeUpdate();
			return true;
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}
	public List<Item> findItemsByKeyword(String keyword) {
		List<Item> retValue = new ArrayList<Item>();
		final String QUERY_ALL = "SELECT * from items, revisions where items.item_latest = revisions.rev_id and items.item_title like ?";
		Connection conn = null;
		try{
			conn = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = conn.prepareStatement(QUERY_ALL);
			queryPs.setString(1, "%" + keyword + "%");
			ResultSet rs = queryPs.executeQuery();
			while (rs.next()){
				Item it = new Item();
				it.setTitle(rs.getString("item_title"));
				it.setType(rs.getInt("item_type"));
				it.setEditor(rs.getString("editor"));
				it.setEditTime(rs.getString("time"));
				it.setContent(rs.getString("content"));
				it.setItemId(rs.getInt("item_id"));
				it.setRevId(rs.getInt("rev_id"));
				retValue.add(it);
			}
			return retValue;
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	public List<Item> findItemsByName(String name) {
		List<Item> retValue = new ArrayList<Item>();
		final String QUERY_ALL = "SELECT * from items where item_title like ?";
		Connection conn = null;
		try{
			conn = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = conn.prepareStatement(QUERY_ALL);
			queryPs.setString(1, name);
			ResultSet rs = queryPs.executeQuery();
			if (rs.next()){
				Item it = new Item();
				it.setTitle(rs.getString("item_title"));
				it.setType(rs.getInt("item_type"));
				it.setItemId(rs.getInt("item_id"));
				retValue.add(it);
				return retValue;
			} else {
				return null;
			}			
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public Item delItemByItemId(int id){
		Connection connection = null;
		try{
			final String QUERY_SQL = "SELECT * from items, revisions where items.item_latest = revisions.rev_id and items.item_id = ?";
			final String DEL_SQL2 = "delete from revisions where item_id = ?";
			final String DEL_SQL3 = "delete from items where item_id = ?";
			connection = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = connection.prepareStatement(QUERY_SQL);
			PreparedStatement delPs2 = connection.prepareStatement(DEL_SQL2);
			PreparedStatement delPs3 = connection.prepareStatement(DEL_SQL3);
			queryPs.setInt(1, id);
			delPs2.setInt(1, id);
			delPs3.setInt(1, id);
			ResultSet rs = queryPs.executeQuery();
			try {
				delPs2.executeUpdate();
				delPs3.executeUpdate();
			} catch (Exception e) {
				System.err.println(e);
			}
			if (rs.next()){
				Item it = new Item();
				it.setTitle(rs.getString("item_title"));
				it.setType(rs.getInt("item_type"));
				it.setEditor(rs.getString("editor"));
				it.setEditTime(rs.getString("time"));
				it.setContent(rs.getString("content"));
				it.setItemId(rs.getInt("item_id"));
				it.setRevId(rs.getInt("rev_id"));
				return it;
			}
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public Item setAttribute(String attribute, String value){
		Connection connection = null;
		try{
			final String QUERY_SQL1 = "SELECT * from items, revisions where items.item_latest = revisions.rev_id and items.item_id = ?";
			final String QUERY_SQL2 = "SELECT * from attrs where attr_name = ?";
			final String INSERT_SQL = "INSERT INTO attrs (attr_name, attr_value) value (?, ?)";
			final String UPDATE_SQL = "UPDATE attrs SET attr_value = ? where attr_name = ?";
			connection = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs1 = connection.prepareStatement(QUERY_SQL1);
			PreparedStatement queryPs2 = connection.prepareStatement(QUERY_SQL2);
			PreparedStatement insertPs = connection.prepareStatement(INSERT_SQL);
			PreparedStatement updatePs = connection.prepareStatement(UPDATE_SQL);
			queryPs1.setString(1, value);
			queryPs2.setString(1, attribute);
			insertPs.setString(1, attribute);
			insertPs.setString(2, value);
			updatePs.setString(1, value);
			updatePs.setString(2, attribute);
			ResultSet rs1 = queryPs1.executeQuery();
			Item it = new Item();
			if (rs1.next()){
				it.setTitle(rs1.getString("item_title"));
				it.setType(rs1.getInt("item_type"));
				it.setEditor(rs1.getString("editor"));
				it.setEditTime(rs1.getString("time"));
				it.setContent(rs1.getString("content"));
				it.setItemId(rs1.getInt("item_id"));
				it.setRevId(rs1.getInt("rev_id"));				
			}
			ResultSet rs2 = queryPs2.executeQuery();
			if (rs2.next()) {
				try {
					updatePs.executeUpdate();
				} catch (Exception e) {
					System.err.println(e);
					return null;
				}
			} else {
				try {
					insertPs.executeUpdate();
				} catch (Exception e) {
					System.err.println(e);
					return null;
				}
			}			
			return it;			
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public String getAttributeValue(String attribute){
		Connection connection = null;
		try{
			final String QUERY_SQL2 = "SELECT * from attrs where attr_name = ?";
			connection = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs2 = connection.prepareStatement(QUERY_SQL2);
			queryPs2.setString(1, attribute);
			ResultSet rs2 = queryPs2.executeQuery();
			if (rs2.next()) {
				return rs2.getString("attr_value");
			}
			return null;			
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
}
